clear

* Import LLM info and merge with municipality 
* Source ISTAT: https://www.istat.it/it/archivio/252261 

import excel "$data\RACCORDO COMUNI2011_SLL2011.xlsx", sheet("COM_SLL_2011") firstrow clear
rename ISTAT_2011 istat2011
rename REG   region
rename COMUNE_2011 mun2011   
rename SLL_2011  llm
rename DEN_SLL_2011  llmname  
rename POP2011 pop2011  
gen istat=istat2011
save "$data\define_llm.dta" , replace


* Import changes municipalities over time
* Source ISTAT: https://www.istat.it/it/archivio/222527

import excel "$data\Variazioni_amministrative_territoriali_dal_01011991.xls", sheet("data") firstrow clear
destring year, replace
keep if year>=1998&year<=2012
drop Tipovariazione CodiceRegione CodiceUnitaterritorialesovraco CodiceRegioneassociatoallava H 
drop ProvvedimentoeDocumento Contenutodelprovvedimento Datadecorrenzavaliditaamminis Flag_note
rename CodiceComuneformatoalfanumeri istatold
rename DenominazioneComune istatnameold
rename CodicedelComuneassociatoalla  istat
rename DenominazioneComuneassociataa istatname
destring istat istatold , replace
duplicates tag istat, gen(rep)
tab rep
drop rep
rename istat istatnew
rename istatold istat
duplicates drop istat, force
save "$data\istatold.dta" , replace

* Merge LLM and changes in municipalities
 
use "$data\define_llm.dta" , clear
merge 1:1 istat using "$data\istatold.dta" 
keep if _m==2
drop _m 
rename istat istatold
rename istatnew istat
keep istatold istat
duplicates tag istat, gen(rep)
tab rep
drop rep
merge m:1 istat using "$data\define_llm.dta" , keep(matched) keepusing(llm llmname)
drop _m istat
rename istatold istat
d
save "$data\istatoldtoadd.dta" , replace

use "$data\define_llm.dta" , clear
append using "$data\istatoldtoadd.dta"
foreach x in region llmname pop2011 {
bys llm: carryforward(`x'), replace
}
keep region llm llmname istat pop2011
order region llm llmname istat pop2011
save "$data\LocalLaborMarkets_define.dta" , replace
export excel using "$data\LocalLaborMarkets_define.xls", firstrow(variables) replace




//  POPOLAZIONE: MOVIMENTO ANNUALE POPOLAZIONE RESIDENTE   
//           Robustness for local labor markets  

clear all
set more off

*  Aggregate population files

 use "$data\P_1995.dta" , clear

label define codici_province 001 "Torino" 002 "Vercelli" 003 "Novara" 004 "Cuneo" 005 "Asti" 006 "Alessandria" 096 "Biella" 103 "Verbano-Cusio-Ossola" ///
007 "Valle D'Aosta/Valle d'Aoste" 012 "Varese" 013 "Como" 014 "Sondrio" 015 "Milano" 016 "Bergamo" 017 "Brescia" 018 "Pavia" 019 "Cremona" 020 "Mantova" 097 "Lecco" 098 "Lodi" 108 ///
"Monza e della Brianza" 021 "Bolzano/Bozen" 022 "Trento" 023 "Verona" 024 "Vicenza" 025 "Belluno" 026 "Treviso" 027 "Venezia" 028 "Padova" 029 "Rovigo" 030 "Udine" 031 "Gorizia" ///
032 "Trieste" 093 "Pordenone" 008 "Imperia" 009 "Savona" 010 "Genova" 011 "La Spezia" 033 "Piacenza" 034 "Parma" 035 "Reggio nell'Emilia" 036 "Modena" 037 "Bologna" 038 "Ferrara" ///
039 "Ravenna" 040 "Forli-Cesena" 099 "Rimini" 045 "Massa-Carrara" 046 "Lucca" 047 "Pistoia" 048 "Firenze" 049 "Livorno" 050 "Pisa" 051 "Arezzo" 052 "Siena" 053 "Grosseto" 100 "Prato" ///
054	"Perugia" 055 "Terni" 041 "Pesaro e Urbino" 042	"Ancona" 043	"Macerata" 044	"Ascoli Piceno" 109	"Fermo" 056	"Viterbo" 057	"Rieti" 058	"Roma" 059	"Latina" 060 "Frosinone" ///
066	"L'Aquila" 067	"Teramo" 068	"Pescara" 069	"Chieti" 070	"Campobasso" 094	"Isernia" 061	"Caserta" 062	"Benevento" 063	"Napoli" 064	"Avellino" 065	"Salerno" ///
071 "Foggia" 072	"Bari" 073	"Taranto" 074	"Brindisi" 075	"Lecce" 110	"Barletta-Andria-Trani" 076	"Potenza" 077	"Matera" 078	"Cosenza" 079	"Catanzaro" ///
080	"Reggio di Calabria" 101	"Crotone" 102	"Vibo Valentia" 081	"Trapani" 082	"Palermo" 083	"Messina" 084	"Agrigento" 085	"Caltanissetta" 086	"Enna" 087	"Catania" ///
088	"Ragusa" 089	"Siracusa" 090	"Sassari" 091	"Nuoro" 092	"Cagliari" 095	"Oristano" 104	"Olbia-Tempio" 105	"Ogliastra" 106	"Medio Campidano" 107	"Carbonia-Iglesias"
label define codici_regioni  1 "Piemonte" 2 "Valle D'Aosta" 3 "Lombardia" 4 "Trentino Alto-Adige" 5 "Veneto" 6 "Friuli Venezia Giulia" 7 "Liguria" 8 "Emilia Romagna" ///
9 "Toscana" 10 "Umbria" 11 "Marche" 12 "Lazio" 13 "Abruzzo" 14 "Molise" 15 "Campania" 16 "Puglia" 17 "Basilicata" 18 "Calabria" 19 "Sicilia" 20 "Sardegna" 

 forvalues t=1996/2012{
  append using "$data\P_`t'.dta" 
  } 
  lab values cod_prov codici_province
  lab values region codici_regioni
  rename mun istat 
  merge m:1 region istat using "$data\LocalLaborMarkets_define.dta" , keep(match master) keepusing(llm)
  tab _merge
  drop _merge 
 
  preserve
  collapse (sum)  mp fp tp , by (year llm region)
  gen flag=1
  bys year region : egen np=sum (flag)
  save "$data\P_dataset_llm.dta" , replace
  restore
    
  
//  MIGRANTI: MOVIMENTO ANNUALE POPOLAZIONE STRANIERA - SESSO E CITTADINANZA   1998-2010 
              
* 2005-2010
forval t=2005/2012{

use "$migranti\migsc_`t'.dta" , clear
label define continente 1 "europa" 2 "africa" 3 "asia" 4 "america" 5 "oceania" 6 "apolidi"
label define subcontinente 11 "EU" 12 "EU CE" 13 "EU other" 21 "AfricaN" 22 "AfricaW" ///
             23 "AfricaE" 24 "AfricaCS" 31 "AsiaW" 32 "AsiaS" 33 "AsiaE" 41 "AmericaN" 42 "AmericaCS" 50 "Oceania" 60 "Apolidi"
label define socioeco 1 "paese avanzato" 2 "paese con pressione migratoria"
rename regione region
rename provincia cod_prov
rename comune cod_mun
rename maschi mmn
lab var mmn "migrant male nationality"
rename femmine fmn
lab var fmn "migrant female nationality"
rename totale tmn
lab var tmn "total migrant nationality"
rename paese nationality
rename continente continent
lab values continent continente
rename sub_area farea
lab var farea "foreign area"
lab values farea subcontinente
rename area_socio_econ seco
lab var seco "socio-economic environment"
lab values seco socioeco
rename anno year 
drop qualit_ v13
collapse (sum) mmn fmn tmn , by(nationality region cod_prov cod_mun continent farea year)
save "$data\MSC_`t'_mun.dta" , replace
}

*2004
use "$migranti\migsc_2004.dta" , clear
label define continente 1 "europa" 2 "africa" 3 "asia" 4 "america" 5 "oceania" 6 "apolidi"
label define subcontinente 11 "EU" 12 "EU CE" 13 "EU other" 21 "AfricaN" 22 "AfricaW" ///
             23 "AfricaE" 24 "AfricaCS" 31 "AsiaW" 32 "AsiaS" 33 "AsiaE" 41 "AmericaN" 42 "AmericaCS" 50 "Oceania" 60 "Apolidi"
label define socioeco 1 "paese avanzato" 2 "paese con pressione migratoria"
rename regione region
rename provincia cod_prov
rename comune cod_mun
rename maschi mmn
lab var mmn "migrant male nationality"
rename femmine fmn
lab var fmn "migrant female nationality"
rename totale tmn
lab var tmn "total migrant nationality"
rename paese_di_cittadinanza nationality
rename continente_appartenenza continent
lab values continent continente
rename sub_area_cont farea
lab var farea "foreign area"
lab values farea subcontinente
rename area_socio_economica_appart seco
lab var seco "socio-economic environment"
lab values seco socioeco
rename anno year 
collapse (sum) mmn fmn tmn , by(nationality region cod_prov cod_mun continent farea year)
save "$data\MSC_2004_mun.dta" , replace

*2003
use "$migranti\migsc_2003.dta" , clear
label define continente 1 "europa" 2 "africa" 3 "asia" 4 "america" 5 "oceania" 6 "apolidi"
label define subcontinente 11 "EU" 12 "EU CE" 13 "EU other" 21 "AfricaN" 22 "AfricaW" ///
             23 "AfricaE" 24 "AfricaCS" 31 "AsiaW" 32 "AsiaS" 33 "AsiaE" 41 "AmericaN" 42 "AmericaCS" 50 "Oceania" 60 "Apolidi"
label define socioeco 1 "paese avanzato" 2 "paese con pressione migratoria"
rename regione region
rename provincia cod_prov
rename comune cod_mun
rename maschi mmn
lab var mmn "migrant male nationality"
rename femmine fmn
lab var fmn "migrant female nationality"
rename totale tmn
lab var tmn "total migrant nationality"
rename paese_di_cittadinanza nationality
rename anno year 
merge m:1 nationality using "$data\farea.dta"
collapse (sum) mmn fmn tmn , by(nationality region cod_prov cod_mun continent farea year)
save "$data\MSC_2003_mun.dta" , replace

*2002
use "$migranti\migsc_2002.dta" , clear
label define continente 1 "europa" 2 "africa" 3 "asia" 4 "america" 5 "oceania" 6 "apolidi"
label define subcontinente 11 "EU" 12 "EU CE" 13 "EU other" 21 "AfricaN" 22 "AfricaW" ///
             23 "AfricaE" 24 "AfricaCS" 31 "AsiaW" 32 "AsiaS" 33 "AsiaE" 41 "AmericaN" 42 "AmericaCS" 50 "Oceania" 60 "Apolidi"
label define socioeco 1 "paese avanzato" 2 "paese con pressione migratoria"
rename regione region
rename provincia cod_prov
rename campo_lib cod_mun
drop cod_mun 
rename maschi mmn
lab var mmn "migrant male nationality"
rename femmine fmn
lab var fmn "migrant female nationality"
rename totale tmn
lab var tmn "total migrant nationality"
rename paese nationality
rename continente continent
lab values continent continente
rename sub_area farea
lab var farea "foreign area"
lab values farea subcontinente
rename area_socio_econ seco
lab var seco "socio-economic environment"
lab values seco socioeco
rename anno year 
collapse (sum) mmn fmn tmn , by(nationality region cod_prov cod_mun continent farea year)
save "$data\MSC_2002_mun.dta" , replace

* 1995-2000
forvalues t=1995/2000{
use "$migranti\migsc_`t'.dta" , clear
label define continente 1 "europa" 2 "africa" 3 "asia" 4 "america" 5 "oceania" 6 "apolidi"
label define subcontinente 11 "EU" 12 "EU CE" 13 "EU other" 21 "AfricaN" 22 "AfricaW" ///
             23 "AfricaE" 24 "AfricaCS" 31 "AsiaW" 32 "AsiaS" 33 "AsiaE" 41 "AmericaN" 42 "AmericaCS" 50 "Oceania" 60 "Apolidi"
label define socioeco 1 "paese avanzato" 2 "paese con pressione migratoria"
rename regione region
rename provincia cod_prov
rename comune cod_mun
rename maschi mmn
lab var mmn "migrant male nationality"
rename femmine fmn
lab var fmn "migrant female nationality"
gen tmn= mmn+fmn
lab var tmn "total migrant nationality"
rename paese_di_cittadinanza nationality
rename continente_di_appartenenza continent
lab values continent continente
rename sub_area_di_continente farea
lab var farea "foreign area"
lab values farea subcontinente
rename area_socio_economica_di_provenie seco
lab var seco "socio-economic environment"
lab values seco socioeco
gen year =`t'
collapse (sum) mmn fmn tmn , by(nationality region cod_prov cod_mun continent farea year)
save "$data\MSC_`t'_mun.dta" , replace
}


*Generate 2001 as average of 2000-2002

local n 2002
forvalues t=2000(2)`n'{
use "$data\MSC_`t'.dta" , clear
foreach var in mmn fmn tmn year {
rename `var' `var'`t' 
destring `var'`t' , replace
}
* check duplicates on merge variables
duplicates report
duplicates tag region cod_prov cod_mun nationality , gen(rep)
tab rep
duplicates drop region cod_prov cod_mun nationality , force
save "$data\MSC_merge`t'_mun.dta" , replace
}
use "$data\MSC_merge2000_mun.dta" , clear
merge 1:1 region cod_prov cod_mun nationality using "$data\MSC_merge2002_mun.dta"
sort _merge
gen year=2001 
egen mmn= rowmean(mmn2000 mmn2002)
egen fmn= rowmean(fmn2000 fmn2002)
egen tmn= rowmean(tmn2000 tmn2002)
replace mmn=mmn2000 if _merge==1
replace mmn=mmn2002 if _merge==2
replace fmn=fmn2000 if _merge==1
replace fmn=fmn2000 if _merge==2
replace tmn=tmn2000 if _merge==1
replace tmn=tmn2000 if _merge==2
egen mmn_med= rowmedian(mmn2000 mmn2002)
egen fmn_med= rowmedian(fmn2000 fmn2002)
egen tmn_med= rowmedian(tmn2000 tmn2002)
foreach var in mmn fmn tmn mmn_med fmn_med tmn_med{
replace `var'=round(`var')
}
gen diff=tmn -(mmn+fmn)
replace tmn=tmn+1 if diff!=0
keep region cod_prov cod_mun nationality farea  year mmn fmn tmn mmn_med fmn_med tmn_med 
save "$data\MSC_2001_mun.dta" , replace


*   Aggregate dataset 1995-2012

set more off
use "$data\MSC_1995_mun.dta" , clear
forvalues t=1996/2012{
append using "$data\MSC_`t'_mun.dta" , force
}
label define cittadinanza  201 "Albania" 202	"Andorra" 203 "Austria" 206	"Belgio" 209 "Bulgaria" 210 "Ex-cecoslovacchia" 212	"Danimarca" 214	"Finlandia" 215	"Francia" 216 "Germania" 219 "Regno Unito" ///	
220	"Grecia" 221 "Irlanda" 223 "Islanda" 225 "Liechtenstein" 226 "Lussemburgo" 227	"Malta" 229	"Monaco" 231 "Norvegia" 232	"Paesi Bassi" 233 "Polonia" 234	"Portogallo" ///	
235	"Romania" 236 "San Marino" 239	"Spagna" 240 "Svezia" 241 "Svizzera" 243 "Ucraina" 244	"Ungheria" 245	"Russa,Federazione" 246	"Santa Sede" 247 "Estonia" 248 "Lettonia" ///
249	"Lituania" 250 "Croazia" 251 "Slovenia" 252	"Bosnia-Erzegovina" 253	"Macedonia,ex Repubblica Jugoslava di" 254 "Moldova" 255 "Slovacchia" 256 "Bielorussia" 257	"Ceca,Repubblica" ///	
270	"Montenegro" 271 "Serbia, Repubblica di" 301 "Afghanistan" 302 "Arabia Saudita" 304	"Bahrein" 305 "Bangladesh" 306 "Bhutan" 307	"Myanmar (ex Birmania)" 309	"Brunei" ///
310	"Cambogia" 311 "Sri Lanka (ex Ceylon)" 314	"Cinese, Repubblica Popolare" 315 "Cipro" 319 "Corea, Repubblica Popolare Democratica (Corea del Nord)" ///
320	"Corea, Repubblica (Corea del Sud)" 322	"Emirati Arabi Uniti" 323	"Filippine" 324	"Territori dell'Autonomia Palestinese" 	326	"Giappone" 	327	"Giordania" 330	"India" ///	
331	"Indonesia" 332	"Iran, Repubblica Islamica del" 333	"Iraq" 334	"Israele" 335 "Kuwait" 336 "Laos" 337 "Libano" 338 "Timor Orientale" 339 "Maldive" 340 "Malaysia" 341 "Mongolia" ///	
342	"Nepal" 343	"Oman" 344 "Pakistan" 345 "Qatar" 346	"Singapore" 348	"Siria" 349	"Thailandia" 351 "Turchia" 353 "Vietnam" 354 "Yemen" 356 "Kazakhstan" 357 "Uzbekistan" ///	
358	"Armenia" 359 "Azerbaigian" 360	"Georgia" 361 "Kirghizistan" 362 "Tagikistan" 363 "Taiwan (ex Formosa)" 364	"Turkmenistan" 401 "Algeria" 402 "Angola" 404 "Costa d'Avorio" ///	
406	"Benin (ex Dahomey)" 408 "Botswana" 409	"Burkina Faso (ex Alto Volta)" 410	"Burundi" 411 "Camerun" 413	"Capo Verde" 414 "Centrafricana, Repubblica" 415 "Ciad" 417	"Comore" ///	
418	"Congo (Repubblica del)" 419 "Egitto" 420 "Etiopia" 421	"Gabon" 422	"Gambia" 423 "Ghana" 424 "Gibuti" 425 "Guinea" 426 "GuineaBissau" 427 "Guinea Equatoriale" 428	"Kenya" ///	
429	"Lesotho" 430	"Liberia" 431 "Libia" 432 "Madagascar" 434	"Malawi" 435 "Mali" 436	"Marocco" 437	"Mauritania" 438 "Mauritius" 440 "Mozambico" 441 "Namibia" 442	"Niger" ///	
443	"Nigeria" 446 "Ruanda" 448	"Sao Tome e Principe" 449 "Seychelles" 450	"Senegal" 451 "Sierra Leone" 453 "Somalia" 454	"Sud Africa" 455 "Sudan" 456 "Swaziland" 457 "Tanzania" ///	
458	"Togo" 460	"Tunisia" 461 "Uganda" 463	"Congo, Repubblica democratica del (ex Zaire)" 464 "Zambia" 465	"Zimbabwe (ex Rhodesia)" 466 "Eritrea" 503 "Antigua e Barbuda" ///	
505	"Bahamas" 506 "Barbados" 507 "Belize" 509 "Canada" 513	"Costa Rica" 514 "Cuba" 515	"Dominica" 516	"Dominicana, Repubblica" 517 "El Salvador" 518	"Giamaica" 519 "Grenada" ///	
523	"Guatemala" 524	"Haiti" 525	"Honduras" 527	"Messico" 529 "Nicaragua" 530 "Panama" 532	"Saint Lucia" 533 "Saint Vincent e Grenadine" 534 "Saint Kitts e Nevis" ///
536	"Stati Uniti d'America" 602	"Argentina" 604	"Bolivia" 605	"Brasile" 606 "Cile" 608 "Colombia" 609	"Ecuador" 612 "Guyana" 614	"Paraguay" 615	"Peru" 616 "Suriname" ///	
617	"Trinidad e Tobago" 618	"Uruguay" 619 "Venezuela" 701 "Australia" 703 "Figi" 708 "Kiribati" 712	"Marshall, Isole" 713 "Micronesia, Stati Federati" 715 "Nauru" 719 "Nuova Zelanda" ///	
720	"Palau" 721	"Papua Nuova Guinea" 725 "Salomone, Isole" 727	"Samoa" 730	"Tonga" 731	"Tuvalu" 732 "Vanuatu" 999 "APOLIDE" 888 "altro" 777 "non indicato"
lab values nationality cittadinanza
lab values cod_prov codici_province
keep  year region cod_prov cod_mun nationality farea mmn fmn tmn mmn_med fmn_med tmn_med
save "$data\MSC_dataset_mun.dta" , replace



//  MIGRANTI: MOVIMENTO ANNUALE POPOLAZIONE STRANIERA  BILANCIO ANNUALE   

forvalues t=1995/2000{
 use "$migranti\migb_`t'.dta" , clear
 rename regione region
 rename anno year
 rename prov cod_prov 
 rename com cod_mun 
 rename m_popfin  mm
 lab var mm "male migrant population"
 rename f_popfin  fm 
 lab var fm "female migrant population"
 rename m_f_popfin tm
 lab var tm "total migrant population"
 rename m_minsti mm_less18
 lab var mm_less18 "male minor migrants"
 rename f_minsti fm_less18
 lab var fm_less18 "female minor migrants"
 rename m_f_minsti tm_less18
 lab var tm_less18 "total minor migrants"
 rename comune COMUNE
 collapse (sum) mm fm tm , by( region cod_prov cod_mun year)
 keep region year cod_prov cod_mun mm fm tm 
 save "$data\MIGB_`t'.dta" , replace
 }
 
 forvalues t=2002/2012{
 use "$migranti\migb_`t'.dta" , clear
 rename regione region
 rename anno year
 rename prov cod_prov 
 rename comune cod_mun 
 rename m_popfin  mm
 lab var mm "male migrant population"
 rename f_popfin  fm 
 lab var fm "female migrant population"
 rename m_f_popfin tm
 lab var tm "total migrant population"
 rename m_minsti mm_less18
 lab var mm_less18 "male minor migrants"
 rename f_minsti fm_less18
 lab var fm_less18 "female minor migrants"
 rename m_f_minsti tm_less18
 lab var tm_less18 "total minor migrants"
 collapse (sum) mm fm tm , by( region cod_prov cod_mun year)
 keep region year cod_prov cod_mun mm fm tm 
 save "$data\MIGB_`t'.dta" , replace
 }
 
// Generate 2001 as mean 2000-2002

forvalues t=2000(2)2002{
use "$data\MIGB_`t'.dta" , clear
foreach var in mm fm tm year {
rename `var' `var'`t' 
destring `var'`t' , replace
}
* check duplicates on merge variables
duplicates report
duplicates tag region cod_prov cod_mun   , gen(rep)
duplicates drop region cod_prov cod_mun  , force
save "$data\MIGB_merge`t'.dta" , replace
}
use "$data\MIGB_merge2000.dta" , clear
merge 1:1 region cod_prov cod_mun using "$data\MIGB_merge2002.dta"
sort _merge
gen year=2001 
foreach var in mm fm tm  {
egen `var'= rowmean(`var'2000 `var'2002)
egen `var'_med= rowmedian(`var'2000 `var'2002)
replace `var'=round(`var')
replace `var'_med=round(`var'_med)
}
keep region cod_prov year cod_mun mm fm tm mm_med fm_med tm_med 
save "$data\MIGB_2001.dta" , replace


* Aggregate dataset

use "$data\MIGB_1995.dta" , clear
forvalues t=1996/2012{
append using "$data\MIGB_`t'.dta"
}
label define codici_province 001 "Torino" 002 "Vercelli" 003 "Novara" 004 "Cuneo" 005 "Asti" 006 "Alessandria" 096 "Biella" 103 "Verbano-Cusio-Ossola" ///
007 "Valle D'Aosta/Valle d'Aoste" 012 "Varese" 013 "Como" 014 "Sondrio" 015 "Milano" 016 "Bergamo" 017 "Brescia" 018 "Pavia" 019 "Cremona" 020 "Mantova" 097 "Lecco" 098 "Lodi" 108 ///
"Monza e della Brianza" 021 "Bolzano/Bozen" 022 "Trento" 023 "Verona" 024 "Vicenza" 025 "Belluno" 026 "Treviso" 027 "Venezia" 028 "Padova" 029 "Rovigo" 030 "Udine" 031 "Gorizia" ///
032 "Trieste" 093 "Pordenone" 008 "Imperia" 009 "Savona" 010 "Genova" 011 "La Spezia" 033 "Piacenza" 034 "Parma" 035 "Reggio nell'Emilia" 036 "Modena" 037 "Bologna" 038 "Ferrara" ///
039 "Ravenna" 040 "Forli-Cesena" 099 "Rimini" 045 "Massa-Carrara" 046 "Lucca" 047 "Pistoia" 048 "Firenze" 049 "Livorno" 050 "Pisa" 051 "Arezzo" 052 "Siena" 053 "Grosseto" 100 "Prato" ///
054	"Perugia" 055 "Terni" 041 "Pesaro e Urbino" 042	"Ancona" 043	"Macerata" 044	"Ascoli Piceno" 109	"Fermo" 056	"Viterbo" 057	"Rieti" 058	"Roma" 059	"Latina" 060 "Frosinone" ///
066	"L'Aquila" 067	"Teramo" 068	"Pescara" 069	"Chieti" 070	"Campobasso" 094	"Isernia" 061	"Caserta" 062	"Benevento" 063	"Napoli" 064	"Avellino" 065	"Salerno" ///
071 "Foggia" 072	"Bari" 073	"Taranto" 074	"Brindisi" 075	"Lecce" 110	"Barletta-Andria-Trani" 076	"Potenza" 077	"Matera" 078	"Cosenza" 079	"Catanzaro" ///
080	"Reggio di Calabria" 101	"Crotone" 102	"Vibo Valentia" 081	"Trapani" 082	"Palermo" 083	"Messina" 084	"Agrigento" 085	"Caltanissetta" 086	"Enna" 087	"Catania" ///
088	"Ragusa" 089	"Siracusa" 090	"Sassari" 091	"Nuoro" 092	"Cagliari" 095	"Oristano" 104	"Olbia-Tempio" 105	"Ogliastra" 106	"Medio Campidano" 107	"Carbonia-Iglesias"
label define codici_regioni  1 "Piemonte" 2 "Valle D'Aosta" 3 "Lombardia" 4 "Trentino Alto-Adige" 5 "Veneto" 6 "Friuli Venezia Giulia" 7 "Liguria" 8 "Emilia Romagna" ///
9 "Toscana" 10 "Umbria" 11 "Marche" 12 "Lazio" 13 "Abruzzo" 14 "Molise" 15 "Campania" 16 "Puglia" 17 "Basilicata" 18 "Calabria" 19 "Sicilia" 20 "Sardegna" 
lab values cod_prov codici_province
lab values region codici_regioni
 
global varlist mm fm tm  mm_med fm_med tm_med 
duplicates report
duplicates tag region cod_prov cod_mun year , gen(rep)
tab rep
duplicates drop region cod_prov cod_mun year , force 
drop rep
keep  year region cod_prov cod_mun mm fm tm mm_med fm_med tm_med
save "$data\MIGB_dataset_mun.dta" , replace
 

 
 
* Generate population vector by origin, mun (prov), year
* 1. Merge population data (aggregate data) with migrant bilancio data (aggregate)

use "$data\MIGB_dataset_mun.dta" , clear
gen mun=string(cod_prov,"%03.0f")+string(cod_mun,"%03.0f")
rename mun istat 
merge m:1 region istat using "$data\LocalLaborMarkets_define.dta" , keep(match master) keepusing(llm)
tab _merge
drop _merge 

merge 1:1 region llm year using "$data\P_dataset_llm.dta"
tab _merge
drop _merge

gen ti= tp-tm
lab var ti "total italian population"
gen mi= mp-mm
lab var mi "male italian population"
gen fi= fp-fm
lab var fi "female italian population"
order year region llm tp mp fp mi fi ti 
keep year region llm tp mp fp mi fi ti 
rename mi mmn
rename fi fmn
rename ti tmn
gen nationality=998 // Italian 
save "$data\PI_dataset_llm.dta" , replace

 
* 2. Append PI_data with migration (sex and citizenship) data
* Complete vector of pop my nationality by mun, year (including italian)

use "$data\MSC_dataset_mun.dta" , clear
gen mun=string(cod_prov,"%03.0f")+string(cod_mun,"%03.0f")
rename mun istat 
merge m:1 region istat using "$data\LocalLaborMarkets_define.dta" , keep(match master) keepusing(llm)
tab _merge
drop _merge 

append using "$data\PI_dataset_llm.dta" 
collapse (sum) tmn mmn fmn tp mp fp, by(llm year nationality)
bys year llm  : egen TP= max(tp)
bys year llm  : egen MP= max(mp)
bys year llm  : egen FP= max(fp)
gen qt_ny=  (tmn/TP)*100
lab var qt_ny "perc. tot migrant by nationality-llm-year"
gen qm_ny=  (mmn/MP)*100
lab var qm_ny "perc. male migrant by nationality-llm-year"
gen qf_ny=  (fmn/FP)*100
lab var qf_ny "perc. female migrant by nationality-llm-year"

* Generate vector Q by j-ethnic groups, by mun and year
* Include different ethnic group classifications

cap drop farea7 farea
drop if nationality==999
rename nationality nat
merge m:1 nat using "$data\CodiciStatiEsteri.dta" , keep(master match) keepusing(nat farea EUII)
tab _merge
drop _merge
replace EUII=4 if nat==224 
replace EUII=4 if nat==258  
replace EUII=4 if nat==259  
replace EUII=4 if nat==260  
replace EUII=4 if nat==261  
replace EUII=6 if nat==317  
tab nat if EUII==.
save "$data\Q_data_country_llm.dta", replace


use "$data\Q_data_country_llm.dta" , clear
recode nationality (998=0) 
preserve
collapse (mean) TP , by(llm)
rename TP population 
save "$data\population_byllm.dta" , replace
restore 




*  Generate change in immigrants inflows over time - local labor markets

 
use "$data\Q_data_country_llm.dta" , clear
sort  nationality llm year

keep if year<=2001 

gen mn2001 =mmn if year==2001
gen mn2000 =mmn if year==2000
gen mn1999 =mmn if year==1999
gen mn1998 =mmn if year==1998
gen mn1997 =mmn if year==1997
gen mn1996 =mmn if year==1996
gen mn1995 =mmn if year==1995

bys nationality llm : egen M2001= max(mn2001)
bys nationality llm : egen M2000= max(mn2000)
bys nationality llm : egen M1999= max(mn1999)
bys nationality llm : egen M1998= max(mn1998)
bys nationality llm : egen M1997= max(mn1997)
bys nationality llm : egen M1996= max(mn1996)
bys nationality llm : egen M1995= max(mn1995)

gen Mdelta_all= M2001-M1995 
replace Mdelta_all= M2001-M1996 if Mdelta_all==. & M1996!=. 
replace Mdelta_all= M2001-M1997 if Mdelta_all==. & M1997!=. 
replace Mdelta_all= M2001-M1998 if Mdelta_all==. & M1998!=. 
replace Mdelta_all= M2001-M1999 if Mdelta_all==. & M1999!=. 
replace Mdelta_all= M2001-M2000 if Mdelta_all==. & M2000!=. 

replace Mdelta_all= M2000-M1995 if Mdelta_all==. & M1995!=. & M2000!=.
replace Mdelta_all= M2000-M1996 if Mdelta_all==. & M1996!=. & M2000!=.
replace Mdelta_all= M2000-M1997 if Mdelta_all==. & M1997!=. & M2000!=.
replace Mdelta_all= M2000-M1998 if Mdelta_all==. & M1998!=. & M2000!=.
replace Mdelta_all= M2000-M1999 if Mdelta_all==. & M1999!=. & M2000!=.

replace Mdelta_all= M1999-M1995 if Mdelta_all==. & M1995!=. & M1999!=.
replace Mdelta_all= M1999-M1996 if Mdelta_all==. & M1996!=. & M1999!=.
replace Mdelta_all= M1999-M1997 if Mdelta_all==. & M1997!=. & M1999!=.
replace Mdelta_all= M1999-M1998 if Mdelta_all==. & M1998!=. & M1999!=.

replace Mdelta_all= M1998-M1995 if Mdelta_all==. & M1995!=. & M1998!=.
replace Mdelta_all= M1998-M1996 if Mdelta_all==. & M1996!=. & M1998!=.
replace Mdelta_all= M1998-M1997 if Mdelta_all==. & M1997!=. & M1998!=.

replace Mdelta_all= M1997-M1995 if Mdelta_all==. & M1995!=. & M1997!=.
replace Mdelta_all= M1997-M1996 if Mdelta_all==. & M1996!=. & M1997!=.


gen fn2001 =fmn if year==2001
gen fn2000 =fmn if year==2000
gen fn1999 =fmn if year==1999
gen fn1998 =fmn if year==1998
gen fn1997 =fmn if year==1997
gen fn1996 =fmn if year==1996
gen fn1995 =fmn if year==1995

bys nationality llm : egen F2001= max(fn2001)
bys nationality llm : egen F2000= max(fn2000)
bys nationality llm : egen F1999= max(fn1999)
bys nationality llm : egen F1998= max(fn1998)
bys nationality llm : egen F1997= max(fn1997)
bys nationality llm : egen F1996= max(fn1996)
bys nationality llm : egen F1995= max(fn1995)

gen Fdelta_all= F2001-F1995 
replace Fdelta_all= F2001-F1996 if Fdelta_all==. & F1996!=. 
replace Fdelta_all= F2001-F1997 if Fdelta_all==. & F1997!=. 
replace Fdelta_all= F2001-F1998 if Fdelta_all==. & F1998!=. 
replace Fdelta_all= F2001-F1999 if Fdelta_all==. & F1999!=. 
replace Fdelta_all= F2001-F2000 if Fdelta_all==. & F2000!=. 

replace Fdelta_all= F2000-F1995 if Fdelta_all==. & F1995!=. & F2000!=.
replace Fdelta_all= F2000-F1996 if Fdelta_all==. & F1996!=. & F2000!=.
replace Fdelta_all= F2000-F1997 if Fdelta_all==. & F1997!=. & F2000!=.
replace Fdelta_all= F2000-F1998 if Fdelta_all==. & F1998!=. & F2000!=.
replace Fdelta_all= F2000-F1999 if Fdelta_all==. & F1999!=. & F2000!=.

replace Fdelta_all= F1999-F1995 if Fdelta_all==. & F1995!=. & F1999!=.
replace Fdelta_all= F1999-F1996 if Fdelta_all==. & F1996!=. & F1999!=.
replace Fdelta_all= F1999-F1997 if Fdelta_all==. & F1997!=. & F1999!=.
replace Fdelta_all= F1999-F1998 if Fdelta_all==. & F1998!=. & F1999!=.

replace Fdelta_all= F1998-F1995 if Fdelta_all==. & F1995!=. & F1998!=.
replace Fdelta_all= F1998-F1996 if Fdelta_all==. & F1996!=. & F1998!=.
replace Fdelta_all= F1998-F1997 if Fdelta_all==. & F1997!=. & F1998!=.

replace Fdelta_all= F1997-F1995 if Fdelta_all==. & F1995!=. & F1997!=.
replace Fdelta_all= F1997-F1996 if Fdelta_all==. & F1996!=. & F1997!=.

sort nationality llm year
bys nationality llm : gen Mdelta= mmn[_n+1]-mmn
gen Mrate= Mdelta/Mdelta_all
bys nationality llm : gen Fdelta= fmn[_n+1]-fmn
gen Frate= Fdelta/Fdelta_all
keep llm year nationality Mrate Frate mmn fmn 
recode nationality (998=0) 
gen bc= nationality 
save "$data\census1991_share_llm.dta" , replace


* Use single data from Census 2001, 1991: males and females

use "$data\Msinglemag_2001_country_llm.dta" , clear
merge 1:1 llm hbc using "$data\Msinglemag_1991_country_llm.dta" , keep(match )
tab _merge
drop _merge
gen Mdelta_singleall= (msinglemag_2001-msinglemag_1991)*(70/100)
cap drop year
save "$data\Mdelta_single_2001-1991_llm.dta"  , replace

use "$data\census1991_share_llm.dta" , clear
rename bc hbc
merge m:1 llm hbc using "$data\Mdelta_single_2001-1991_llm.dta" , keep(match)
tab _merge
drop _merge
gsort llm hbc -year
gen Mdelta_single= Mdelta_singleall*Mrate
gen Msingle= msinglemag_2001 if year==2001
replace Msingle= Msingle[_n-1] - Mdelta_single if year==2000
replace Msingle= Msingle[_n-1] - Mdelta_single if year==1999
replace Msingle= Msingle[_n-1] - Mdelta_single if year==1998
replace Msingle= Msingle[_n-1] - Mdelta_single if year==1997
replace Msingle= Msingle[_n-1] - Mdelta_single if year==1996
replace Msingle= Msingle[_n-1] - Mdelta_single if year==1995
order llm year hbc Msingle msinglemag_2001 msinglemag_1991
drop if year==2001
save "$data\Msinglemag_country_1995-2000_llm.dta" , replace


use "$data\Fsinglemag_2001_country_llm.dta" , clear
merge 1:1 llm wbc using "$data\Fsinglemag_1991_country_llm.dta" , keep(match )
tab _merge
drop _merge
gen Fdelta_singleall= (fsinglemag_2001-fsinglemag_1991)*(70/100)
cap drop year
save "$data\Fdelta_single_2001-1991_llm.dta"  , replace

use "$data\census1991_share_llm.dta" , clear
rename bc wbc
merge m:1 llm wbc using "$data\Fdelta_single_2001-1991_llm.dta" , keep(match)
tab _merge
drop _merge
gsort llm wbc -year
gen Fdelta_single= Fdelta_singleall*Frate
gen Fsingle= fsinglemag_2001 if year==2001
replace Fsingle= Fsingle[_n-1] - Fdelta_single if year==2000
replace Fsingle= Fsingle[_n-1] - Fdelta_single if year==1999
replace Fsingle= Fsingle[_n-1] - Fdelta_single if year==1998
replace Fsingle= Fsingle[_n-1] - Fdelta_single if year==1997
replace Fsingle= Fsingle[_n-1] - Fdelta_single if year==1996
replace Fsingle= Fsingle[_n-1] - Fdelta_single if year==1995
order llm year wbc Fsingle fsinglemag_2001 fsinglemag_1991
drop if year==2001
save "$data\Fsinglemag_country_1995-2000_llm.dta" , replace



use "$data\Q_data_country_llm.dta" , clear
sort  nationality llm year

gen mn2011 =mmn if year==2011
gen mn2010 =mmn if year==2010
gen mn2009 =mmn if year==2009
gen mn2008 =mmn if year==2008
gen mn2007 =mmn if year==2007
gen mn2006 =mmn if year==2006
gen mn2005 =mmn if year==2005
gen mn2004 =mmn if year==2004
gen mn2003 =mmn if year==2003
gen mn2002 =mmn if year==2002
gen mn2001 =mmn if year==2001

bys nationality llm : egen M2011= max(mn2011)
bys nationality llm : egen M2010= max(mn2010)
bys nationality llm : egen M2009= max(mn2009)
bys nationality llm : egen M2008= max(mn2008)
bys nationality llm : egen M2007= max(mn2007)
bys nationality llm : egen M2006= max(mn2006)
bys nationality llm : egen M2005= max(mn2005)
bys nationality llm : egen M2004= max(mn2004)
bys nationality llm : egen M2003= max(mn2003)
bys nationality llm : egen M2002= max(mn2002)
bys nationality llm : egen M2001= max(mn2001)

gen Mdelta_all= M2011-M2001 
replace Mdelta_all= M2011-M2001 if Mdelta_all==. & M2001!=. 
replace Mdelta_all= M2011-M2002 if Mdelta_all==. & M2002!=. 
replace Mdelta_all= M2011-M2003 if Mdelta_all==. & M2003!=. 
replace Mdelta_all= M2011-M2004 if Mdelta_all==. & M2004!=. 
replace Mdelta_all= M2011-M2005 if Mdelta_all==. & M2005!=. 
replace Mdelta_all= M2011-M2006 if Mdelta_all==. & M2006!=. 
replace Mdelta_all= M2011-M2007 if Mdelta_all==. & M2007!=. 
replace Mdelta_all= M2011-M2008 if Mdelta_all==. & M2008!=. 
replace Mdelta_all= M2011-M2009 if Mdelta_all==. & M2009!=. 
replace Mdelta_all= M2011-M2010 if Mdelta_all==. & M2010!=. 

replace Mdelta_all= M2010-M2001 if Mdelta_all==. & M2001!=. & M2010!=.
replace Mdelta_all= M2010-M2002 if Mdelta_all==. & M2002!=. & M2010!=.
replace Mdelta_all= M2010-M2003 if Mdelta_all==. & M2003!=. & M2010!=.
replace Mdelta_all= M2010-M2004 if Mdelta_all==. & M2004!=. & M2010!=.
replace Mdelta_all= M2010-M2005 if Mdelta_all==. & M2005!=. & M2010!=.
replace Mdelta_all= M2010-M2006 if Mdelta_all==. & M2006!=. & M2010!=.
replace Mdelta_all= M2010-M2007 if Mdelta_all==. & M2007!=. & M2010!=.
replace Mdelta_all= M2010-M2008 if Mdelta_all==. & M2008!=. & M2010!=.
replace Mdelta_all= M2010-M2009 if Mdelta_all==. & M2009!=. & M2010!=.

replace Mdelta_all= M2009-M2001 if Mdelta_all==. & M2001!=. & M2009!=.
replace Mdelta_all= M2009-M2002 if Mdelta_all==. & M2002!=. & M2009!=.
replace Mdelta_all= M2009-M2003 if Mdelta_all==. & M2003!=. & M2009!=.
replace Mdelta_all= M2009-M2004 if Mdelta_all==. & M2004!=. & M2009!=.
replace Mdelta_all= M2009-M2005 if Mdelta_all==. & M2005!=. & M2009!=.
replace Mdelta_all= M2009-M2006 if Mdelta_all==. & M2006!=. & M2009!=.
replace Mdelta_all= M2009-M2007 if Mdelta_all==. & M2007!=. & M2009!=.
replace Mdelta_all= M2009-M2008 if Mdelta_all==. & M2008!=. & M2009!=.

replace Mdelta_all= M2008-M2001 if Mdelta_all==. & M2001!=. & M2008!=.
replace Mdelta_all= M2008-M2002 if Mdelta_all==. & M2002!=. & M2008!=.
replace Mdelta_all= M2008-M2003 if Mdelta_all==. & M2003!=. & M2008!=.
replace Mdelta_all= M2008-M2004 if Mdelta_all==. & M2004!=. & M2008!=.
replace Mdelta_all= M2008-M2005 if Mdelta_all==. & M2005!=. & M2008!=.
replace Mdelta_all= M2008-M2006 if Mdelta_all==. & M2006!=. & M2008!=.
replace Mdelta_all= M2008-M2007 if Mdelta_all==. & M2007!=. & M2008!=.

replace Mdelta_all= M2007-M2001 if Mdelta_all==. & M2001!=. & M2007!=.
replace Mdelta_all= M2007-M2002 if Mdelta_all==. & M2002!=. & M2007!=.
replace Mdelta_all= M2007-M2003 if Mdelta_all==. & M2003!=. & M2007!=.
replace Mdelta_all= M2007-M2004 if Mdelta_all==. & M2004!=. & M2007!=.
replace Mdelta_all= M2007-M2005 if Mdelta_all==. & M2005!=. & M2007!=.
replace Mdelta_all= M2007-M2006 if Mdelta_all==. & M2006!=. & M2007!=.

replace Mdelta_all= M2006-M2001 if Mdelta_all==. & M2001!=. & M2006!=.
replace Mdelta_all= M2006-M2002 if Mdelta_all==. & M2002!=. & M2006!=.
replace Mdelta_all= M2006-M2003 if Mdelta_all==. & M2003!=. & M2006!=.
replace Mdelta_all= M2006-M2004 if Mdelta_all==. & M2004!=. & M2006!=.
replace Mdelta_all= M2006-M2005 if Mdelta_all==. & M2005!=. & M2006!=.

replace Mdelta_all= M2005-M2001 if Mdelta_all==. & M2001!=. & M2005!=.
replace Mdelta_all= M2005-M2002 if Mdelta_all==. & M2002!=. & M2005!=.
replace Mdelta_all= M2005-M2003 if Mdelta_all==. & M2003!=. & M2005!=.
replace Mdelta_all= M2005-M2004 if Mdelta_all==. & M2004!=. & M2005!=.

replace Mdelta_all= M2004-M2001 if Mdelta_all==. & M2001!=. & M2004!=.
replace Mdelta_all= M2004-M2002 if Mdelta_all==. & M2002!=. & M2004!=.
replace Mdelta_all= M2004-M2003 if Mdelta_all==. & M2003!=. & M2004!=.

replace Mdelta_all= M2003-M2001 if Mdelta_all==. & M2001!=. & M2003!=.
replace Mdelta_all= M2003-M2002 if Mdelta_all==. & M2002!=. & M2003!=.


gen Fn2011 =fmn if year==2011
gen Fn2010 =fmn if year==2010
gen Fn2009 =fmn if year==2009
gen Fn2008 =fmn if year==2008
gen Fn2007 =fmn if year==2007
gen Fn2006 =fmn if year==2006
gen Fn2005 =fmn if year==2005
gen Fn2004 =fmn if year==2004
gen Fn2003 =fmn if year==2003
gen Fn2002 =fmn if year==2002
gen Fn2001 =fmn if year==2001

bys nationality llm : egen F2011= max(Fn2011)
bys nationality llm : egen F2010= max(Fn2010)
bys nationality llm : egen F2009= max(Fn2009)
bys nationality llm : egen F2008= max(Fn2008)
bys nationality llm : egen F2007= max(Fn2007)
bys nationality llm : egen F2006= max(Fn2006)
bys nationality llm : egen F2005= max(Fn2005)
bys nationality llm : egen F2004= max(Fn2004)
bys nationality llm : egen F2003= max(Fn2003)
bys nationality llm : egen F2002= max(Fn2002)
bys nationality llm : egen F2001= max(Fn2001)

gen Fdelta_all= F2011-F2001
replace Fdelta_all= F2011-F2001 if Fdelta_all==. & F2001!=. 
replace Fdelta_all= F2011-F2002 if Fdelta_all==. & F2002!=. 
replace Fdelta_all= F2011-F2003 if Fdelta_all==. & F2003!=. 
replace Fdelta_all= F2011-F2004 if Fdelta_all==. & F2004!=. 
replace Fdelta_all= F2011-F2005 if Fdelta_all==. & F2005!=. 
replace Fdelta_all= F2011-F2006 if Fdelta_all==. & F2006!=. 
replace Fdelta_all= F2011-F2007 if Fdelta_all==. & F2007!=. 
replace Fdelta_all= F2011-F2008 if Fdelta_all==. & F2008!=. 
replace Fdelta_all= F2011-F2009 if Fdelta_all==. & F2009!=. 
replace Fdelta_all= F2011-F2010 if Fdelta_all==. & F2010!=. 

replace Fdelta_all= F2009-F2001 if Fdelta_all==. & F2001!=. & F2009!=.
replace Fdelta_all= F2009-F2002 if Fdelta_all==. & F2002!=. & F2009!=.
replace Fdelta_all= F2009-F2003 if Fdelta_all==. & F2003!=. & F2009!=.
replace Fdelta_all= F2009-F2004 if Fdelta_all==. & F2004!=. & F2009!=.
replace Fdelta_all= F2009-F2005 if Fdelta_all==. & F2005!=. & F2009!=.
replace Fdelta_all= F2009-F2006 if Fdelta_all==. & F2006!=. & F2009!=.
replace Fdelta_all= F2009-F2007 if Fdelta_all==. & F2007!=. & F2009!=.
replace Fdelta_all= F2009-F2008 if Fdelta_all==. & F2008!=. & F2009!=.

replace Fdelta_all= F2008-F2001 if Fdelta_all==. & F2001!=. & F2008!=.
replace Fdelta_all= F2008-F2002 if Fdelta_all==. & F2002!=. & F2008!=.
replace Fdelta_all= F2008-F2003 if Fdelta_all==. & F2003!=. & F2008!=.
replace Fdelta_all= F2008-F2004 if Fdelta_all==. & F2004!=. & F2008!=.
replace Fdelta_all= F2008-F2005 if Fdelta_all==. & F2005!=. & F2008!=.
replace Fdelta_all= F2008-F2006 if Fdelta_all==. & F2006!=. & F2008!=.
replace Fdelta_all= F2008-F2007 if Fdelta_all==. & F2007!=. & F2008!=.

replace Fdelta_all= F2007-F2001 if Fdelta_all==. & F2001!=. & F2007!=.
replace Fdelta_all= F2007-F2002 if Fdelta_all==. & F2002!=. & F2007!=.
replace Fdelta_all= F2007-F2003 if Fdelta_all==. & F2003!=. & F2007!=.
replace Fdelta_all= F2007-F2004 if Fdelta_all==. & F2004!=. & F2007!=.
replace Fdelta_all= F2007-F2005 if Fdelta_all==. & F2005!=. & F2007!=.
replace Fdelta_all= F2007-F2006 if Fdelta_all==. & F2006!=. & F2007!=.

replace Fdelta_all= F2006-F2001 if Fdelta_all==. & F2001!=. & F2006!=.
replace Fdelta_all= F2006-F2002 if Fdelta_all==. & F2002!=. & F2006!=.
replace Fdelta_all= F2006-F2003 if Fdelta_all==. & F2003!=. & F2006!=.
replace Fdelta_all= F2006-F2004 if Fdelta_all==. & F2004!=. & F2006!=.
replace Fdelta_all= F2006-F2005 if Fdelta_all==. & F2005!=. & F2006!=.

replace Fdelta_all= F2005-F2001 if Fdelta_all==. & F2001!=. & F2005!=.
replace Fdelta_all= F2005-F2002 if Fdelta_all==. & F2002!=. & F2005!=.
replace Fdelta_all= F2005-F2003 if Fdelta_all==. & F2003!=. & F2005!=.
replace Fdelta_all= F2005-F2004 if Fdelta_all==. & F2004!=. & F2005!=.

replace Fdelta_all= F2004-F2001 if Fdelta_all==. & F2001!=. & F2004!=.
replace Fdelta_all= F2004-F2002 if Fdelta_all==. & F2002!=. & F2004!=.
replace Fdelta_all= F2004-F2003 if Fdelta_all==. & F2003!=. & F2004!=.

replace Fdelta_all= F2003-F2001 if Fdelta_all==. & F2001!=. & F2003!=.
replace Fdelta_all= F2003-F2002 if Fdelta_all==. & F2002!=. & F2003!=.

keep if year>=2001 

sort nationality llm year
bys nationality llm  : gen Mdelta= mmn-mmn[_n-1]
gen Mrate= Mdelta/Mdelta_all
bys nationality llm  : gen Fdelta= fmn-fmn[_n-1]
gen Frate= Fdelta/Fdelta_all
keep llm year nationality Mrate Frate mmn fmn 
recode nationality (998=0) 
rename nationality hbc
save "$data\census_share_llm.dta" , replace


* Use single data from Census 2011: males and females

use "$data\Msinglemag_2011_country_llm.dta" , clear
merge 1:1 llm hbc using "$data\Msinglemag_2001_country_llm.dta" , keep(match )
tab _merge
drop _merge
gen Mdelta_singleall= msinglemag_2011-msinglemag_2001
cap drop year
save "$data\Mdelta_single_llm.dta"  , replace

use "$data\census_share_llm.dta" , clear
merge m:1 llm hbc using "$data\Mdelta_single_llm.dta" , keep(match)
tab _merge
drop _merge
sort llm hbc year
gen Mdelta_single= Mdelta_singleall*Mrate
gen Msingle= msinglemag_2001 if year==2001
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2002
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2003
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2004
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2005
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2006
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2007
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2008
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2009
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2010
replace Msingle= Msingle[_n-1] + Mdelta_single if year==2011
replace Msingle= Msingle[_n-1] if year==2012
save "$data\Msinglemag_country_2001-2012_llm.dta" , replace


use "$data\Fsinglemag_2011_country_llm.dta" , clear
merge 1:1 llm wbc using "$data\Fsinglemag_2001_country_llm.dta" , keep(match )
tab _merge
drop _merge
gen Fdelta_singleall= fsinglemag_2011-fsinglemag_2001
cap drop year
save "$data\Fdelta_single_llm.dta"  , replace

use "$data\census_share_llm.dta" , clear
rename hbc wbc
merge m:1 llm wbc using "$data\Fdelta_single_llm.dta" , keep(match)
tab _merge
drop _merge
sort llm wbc year
gen Fdelta_single= Fdelta_singleall*Frate
gen Fsingle= fsinglemag_2001 if year==2001
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2002
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2003
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2004
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2005
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2006
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2007
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2008
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2009
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2010
replace Fsingle= Fsingle[_n-1] + Fdelta_single if year==2011
replace Fsingle= Fsingle[_n-1] if year==2012
save "$data\Fsinglemag_country_2001-2012_llm.dta" , replace


* Append final Census data over time by llm

use "$data\Msinglemag_country_2001-2012_llm.dta" , clear
append using "$data\Msinglemag_country_1995-2000_llm.dta"
save "$data\Msinglemag_country_alltime_llm.dta" , replace

use "$data\Fsinglemag_country_2001-2012_llm.dta" , clear
append using "$data\Fsinglemag_country_1995-2000_llm.dta"
save "$data\Fsinglemag_country_alltime_llm.dta" , replace


